package com.es.business.city;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.business.citys.CitysBean;
import com.es.business.citys.CitysBiz;
import com.es.business.province.ProvinceBean;
import com.es.business.province.ProvinceBiz;
import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class CityBiz {
	public int getCityCountByCitysCode(String citysCode) throws Exception{
		Dao dao = new Dao();
		int result = 0;
		String sql = "select count(*) from hrm_city where citys_code = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(citysCode);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				result = Integer.parseInt(rs.getString(1));
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public int getCityCountByProvinceCode(String provinceCode) throws Exception{
		Dao dao = new Dao();
		int result = 0;
		String sql = "select count(*) from hrm_city where province_code = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(provinceCode);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				result = Integer.parseInt(rs.getString(1));
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public boolean insertCity(String code, String value, String level, String pCode, String citysCode) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "insert into hrm_city (city_code, city_name, city_level, province_code,citys_code) values(?,?,?,?,?)";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(code);
		sqlParam.add(value);
		sqlParam.add(level);
		sqlParam.add(pCode);
		sqlParam.add(citysCode);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public PageBean searchCity(String code, String value, String level, String pCode, String citysCode, int curpage) throws Exception{
		Dao dao = new Dao();
		PageBean pb = null;
		String sql = "select city_id, city_code, city_name, city_level, province_code, citys_code from hrm_city ";
		String[] sqlp = {" city_code = ? ", " city_name = ? ", " city_level=? "," province_code=? "," citys_code= ?"};
		String and = " and ";
		String where = " where ";
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam2.add(code);
		sqlParam2.add(value);
		sqlParam2.add(level);
		sqlParam2.add(pCode);
		sqlParam2.add(citysCode);
		boolean flag = false;
		for(int i = 0; i < sqlParam2.size(); ++i){
			String param = sqlParam2.get(i);
			if(param != null && param.length() > 0){
				if(flag){
					sql += and + sqlp[i];	
				}else{
					sql += where + sqlp[i];
					flag = true;
				}
				sqlParam.add(param);
			}
		}
		try{
			PageNavigator pageNav = new PageNavigator();
			int totalC = dao.getAllRow(sql, sqlParam);
			pageNav.setRowTotal(totalC);
			pageNav.setCurpage(curpage);
			if(totalC % pageNav.getPageRow() == 0){
				pageNav.setPageTotal(totalC / pageNav.getPageRow());
			}else{
				pageNav.setPageTotal(totalC / pageNav.getPageRow() + 1);
			}
			pb = dao.queryPageList(sql, pageNav, sqlParam);
			for(int i = 0; i < pb.getMap().size(); ++i){
				HashMap<String, Object> map = pb.getMap().get(i);
				String provinceCode = map.get("province_code").toString();
				if(provinceCode != null && provinceCode.length() > 0){
					ProvinceBean prs = new ProvinceBiz().getProvinceByCode(provinceCode);
					map.put("province_value", prs.getProvinceName());
				}else{
					map.put("province_value", "");
				}
				String cCode = "";
				if(map.get("citys_code") != null)
					 cCode = map.get("citys_code").toString();
				
				if(cCode != null && cCode.length() > 0){
					CitysBean crs = new CitysBiz().getCitysByCode(cCode);
					map.put("citys_value", crs.getCitysValue());
				}else{
					map.put("citys_value", "");
				}
			}
			pb.setParam(sqlParam2);
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return pb;
	}
	
	public boolean deleteCityById(String id) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "delete from hrm_city where city_id = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public CityBean getCityById(String id) throws Exception{
		Dao dao = new Dao();
		CityBean result = null;
		String sql = "select city_code, city_name, city_level, province_code, citys_code from hrm_city where city_id = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				result = new CityBean();
				result.setCityId(id);
				result.setCityCode(rs.getString(1));
				result.setCityName(rs.getString(2));
				result.setCityLevel(rs.getString(3));
				result.setProvinceCode(rs.getString(4));
				result.setCitysCode(rs.getString(5));
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public CityBean getCityByCode(String code) throws Exception{
		Dao dao = new Dao();
		CityBean result = new CityBean();;
		String sql = "select city_id, city_name, city_level, province_code, citys_code from hrm_city where city_code = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(code);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				result.setCityCode(code);
				result.setCityId(rs.getString(1));
				result.setCityName(rs.getString(2));
				result.setCityLevel(rs.getString(3));
				result.setProvinceCode(rs.getString(4));
				result.setCitysCode(rs.getString(5));
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public boolean modifyCityById(String id, String code, String value, String level, String provinceCode, String citysCode) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "update hrm_city set city_code =?, city_name =? ,city_level = ?, province_code = ?, citys_code = ? where city_id =?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(code);
		sqlParam.add(value);
		sqlParam.add(level);
		sqlParam.add(provinceCode);
		sqlParam.add(citysCode);
		sqlParam.add(id);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public ArrayList<CityBean> getCityByProvinceCode(String pCode) throws Exception{
		Dao dao = new Dao();
		ArrayList<CityBean> result = new ArrayList<CityBean>();
		String sql = "select city_id, city_code, city_name, citys_code from hrm_city where province_code =?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(pCode);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				CityBean cityBean = new CityBean();
				cityBean.setCityId(rs.getString(1));
				cityBean.setCityCode(rs.getString(2));
				cityBean.setCityName((rs.getString(3)));
				cityBean.setCitysCode((rs.getString(4)));
				cityBean.setProvinceCode(pCode);
				result.add(cityBean);
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
}
